CREATE TABLE [dbo].[tb_Cursadas]
(
[idCursada] [int] NOT NULL IDENTITY(1, 1),
[Carrera] [int] NOT NULL,
[Curso] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[AnioLectivo] [int] NOT NULL,
[EspacioCurricular] [int] NOT NULL,
[Asignatura] [int] NOT NULL,
[Aprobo] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Promedio] [float] NULL,
[Conducta] [int] NULL,
[Inasistencias] [int] NOT NULL,
[Tardanzas] [int] NOT NULL,
[Sanciones] [int] NOT NULL,
[Alumno] [int] NOT NULL,
[porcInasistencias] [numeric] (18, 0) NULL,
[Estado] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Division] [numeric] (18, 0) NULL,
[Cursada] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Final] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
CREATE TRIGGER [dbo].[deletetb_Cursadas] ON [dbo].[tb_Cursadas]
 FOR DELETE AS 
		 DELETE tb_Notas FROM tb_Notas
		 WHERE Cursada in (SELECT idCursada FROM deleted)
		 DELETE tb_Inasistencias FROM tb_Inasistencias
		 WHERE Cursada in (SELECT idCursada FROM deleted)
		 DELETE tb_AlumnosDeExamen FROM tb_AlumnosDeExamen
		 WHERE Cursada in (SELECT idCursada FROM deleted)

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
CREATE TRIGGER [dbo].[InsertarCursadas] ON [dbo].[tb_Cursadas] 
FOR INSERT
AS

declare @asignatura varchar(80),@message varchar(80),@espaciocurricular int


if NOT EXISTS(select count(*) from tb_cursadas inner join tb_espacioscurriculares on tb_espacioscurriculares.idespaciocurricular = tb_cursadas.espaciocurricular inner join tb_correlatividades on tb_espacioscurriculares.idespaciocurricular = tb_correlatividades.idespaciocurricular where tb_espacioscurriculares.idespaciocurricular = (select espaciocurricular from inserted) and alumno = (select alumno from inserted) and cursada is null) 

print 'ok'

else begin

 if EXISTS (select count(*) from tb_cursadas inner join tb_espacioscurriculares on tb_espacioscurriculares.idespaciocurricular = tb_cursadas.espaciocurricular inner join tb_correlatividades on tb_espacioscurriculares.idespaciocurricular = tb_correlatividades.idespaciocurricular where tb_espacioscurriculares.idespaciocurricular = (select espaciocurricular from inserted) and alumno = (select alumno from inserted) and cursada is null)
SET NOCOUNT ON
set @espaciocurricular = (select top 1 espaciocurricular from inserted)

DECLARE VerCorrelativas CURSOR FOR 


SELECT tb_espacioscurriculares.DESCRIPCION FROM tb_espacioscurriculares  join tb_correlatividades on tb_espacioscurriculares.idespaciocurricular = tb_correlatividades.idespaciocurricular2
 WHERE tb_correlatividades.IDESPACIOCURRICULAR = @espaciocurricular

OPEN VerCorrelativas


FETCH NEXT FROM VerCorrelativas 

INTO @asignatura

WHILE @@FETCH_STATUS = 0

BEGIN


--    PRINT @asignatura
SELECT @message = 'IMPOSIBLE INSCRIBIR NO HA APROBADO,  '+@asignatura

    PRINT @message

    FETCH NEXT FROM VerCorrelativas 
    INTO @asignatura
END 
CLOSE VerCorrelativas
DEALLOCATE VerCorrelativas
ROLLBACK TRANSACTION
end

















GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE TRIGGER [dbo].[updatetb_Cursadas] ON [dbo].[tb_Cursadas]
 FOR UPDATE AS 
 IF (update(idCursada)) begin 
	 SELECT * FROM inserted 
	 IF @@ROWCOUNT = 1 begin 
		 UPDATE tb_Notas
		 SET Cursada = (SELECT idCursada FROM inserted)
		 WHERE tb_Notas.Cursada = (SELECT idCursada FROM deleted)
		 UPDATE tb_Inasistencias
		 SET Cursada = (SELECT idCursada FROM inserted)
		 WHERE tb_Inasistencias.Cursada = (SELECT idCursada FROM deleted)
		 UPDATE tb_AlumnosDeExamen
		 SET Cursada = (SELECT idCursada FROM inserted)
		 WHERE tb_AlumnosDeExamen.Cursada = (SELECT idCursada FROM deleted)
	end  ELSE begin 
		 ROLLBACK TRANSACTION 
 end 
 end
GO
ALTER TABLE [dbo].[tb_Cursadas] ADD CONSTRAINT [PK_tb_Cursadas] PRIMARY KEY NONCLUSTERED  ([idCursada]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [idx_Alumno] ON [dbo].[tb_Cursadas] ([Alumno]) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tb_Cursadas] WITH NOCHECK ADD CONSTRAINT [FK_tb_Cursadas_tb_Alumnos] FOREIGN KEY ([Alumno]) REFERENCES [dbo].[tb_Alumnos] ([idAlumno])
GO
ALTER TABLE [dbo].[tb_Cursadas] WITH NOCHECK ADD CONSTRAINT [FK_tb_Cursadas_tb_Carreras] FOREIGN KEY ([Carrera]) REFERENCES [dbo].[tb_Carreras] ([idCarrera])
GO
ALTER TABLE [dbo].[tb_Cursadas] WITH NOCHECK ADD CONSTRAINT [FK_tb_Cursadas_tb_Conductas] FOREIGN KEY ([Conducta]) REFERENCES [dbo].[tb_Conductas] ([idConducta])
GO
ALTER TABLE [dbo].[tb_Cursadas] WITH NOCHECK ADD CONSTRAINT [FK_tb_Cursadas_tb_EspaciosCurriculares] FOREIGN KEY ([EspacioCurricular]) REFERENCES [dbo].[tb_EspaciosCurriculares] ([idEspacioCurricular])
GO
ALTER TABLE [dbo].[tb_Cursadas] ADD CONSTRAINT [FK_tb_Cursadas_tb_SancionesAlumno] FOREIGN KEY ([Sanciones]) REFERENCES [dbo].[tb_SancionesAlumno] ([IdSancion])
GO
ALTER TABLE [dbo].[tb_Cursadas] NOCHECK CONSTRAINT [FK_tb_Cursadas_tb_Conductas]
GO
